/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package mytools;

import java.sql.*;
import java.util.*;

/**
 *
 * @author xiaoxiao
 */
public class userDAO {

    private Connection con;

    public userDAO() throws Exception {
    }

    public int login(user user) throws SQLException, Exception {
        this.con = dbcon.getConnection();
        int i = 0;
        PreparedStatement ps = null;
        ps = con.prepareStatement("select * from user where username=? and password=?");
        ps.setString(1, user.getUsername());
        ps.setString(2, user.getPassword());
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            i = 1;
        }
        con.close();
        return i;
    }

    public user getlogininfo(user user) throws SQLException, Exception {
        this.con = dbcon.getConnection();
        PreparedStatement ps = null;
        ps = con.prepareStatement("select * from user where username=? and password=?");
        ps.setString(1, user.getUsername());
        ps.setString(2, user.getPassword());
        ResultSet rs = ps.executeQuery();
        user u = new user();
        if (rs.next()) {
            u.setUserid(rs.getInt("userid"));
            u.setEmail(rs.getString("email"));
            u.setUsername(rs.getString("username"));
            u.setPassword(rs.getString("password"));
            u.setQx(rs.getInt("qx"));
        }
        con.close();
        return u;
    }

    public int deleteUser(int id) throws SQLException, Exception {
        this.con = dbcon.getConnection();
        int i = 0;
        Statement st = con.createStatement();
        i = st.executeUpdate("delete from user where userid='" + id + "'");
        con.close();
        return i;
    }

    public int saveUser(user user) throws SQLException, Exception {
        this.con = dbcon.getConnection();
        int i = 0;
        String sql = "insert into user (userid,username,password,email) values(null,?,?,?)";
        PreparedStatement ps = null;
        ps = con.prepareStatement(sql);
        ps.setString(1, user.getUsername());
        ps.setString(2, user.getPassword());
        ps.setString(3, user.getEmail());
        i = ps.executeUpdate();
        ps.close();
        con.close();
        return i;
    }

    public int updateuser(user user) throws SQLException, Exception {
        this.con = dbcon.getConnection();
        int i = 0;
        String sql = "update user set username=?,password=?,email=? where userid=?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, user.getUsername());
        ps.setString(2, user.getPassword());
        ps.setString(3, user.getEmail());
        ps.setInt(4, user.getUserid());
        i = ps.executeUpdate();
        ps.close();
        con.close();
        return i;
    }

    public user getuser(int id) throws SQLException, Exception {
        this.con = dbcon.getConnection();
        user user = null;
        String sql = "select * from user where userid=?";
        PreparedStatement psmt = null;
        psmt = con.prepareStatement(sql);
        psmt.setInt(1, id);
        ResultSet rs = psmt.executeQuery();
        if (rs.next()) {
            user = new user();
            user.setUserid(id);
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setEmail(rs.getString("email"));
        }
        rs.close();
        psmt.close();
        con.close();
        return user;
    }

    public int getuserAmount() throws SQLException, Exception {
        this.con = dbcon.getConnection();
        int i = 0;
        String sql = "select count(*) from user";
        PreparedStatement ps = null;
        ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            i = rs.getInt(1);
        }

        con.close();
        return i;
    }

    public List<user> getuserlist(int pagenumber, int pagesize) throws SQLException, Exception {
        this.con = dbcon.getConnection();
        List<user> list = new ArrayList<user>();
        String sql = "select * from user limit ?,?";
        PreparedStatement ps = null;
        ps = con.prepareStatement(sql);
        ps.setInt(1, (pagenumber - 1) * pagesize);
        ps.setInt(2, pagesize);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            user user = new user();
            user.setUserid(rs.getInt("userid"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setEmail(rs.getString("email"));
            list.add(user);
        }
        con.close();
        return list;
    }
}
